<?php
require ('./Classes/PHPExcel.php');
class test{
    public $dbConfig    = ['HOST'=>'114.55.8.94','USER'=>'yflsDevelpr','PASSWORD'=>'yfls@#tKsjdkvmedia','NAME'=>'sanyuan_test','PORT'=>3306];

    //连接数据库 设置字符集
    public function cqmysqli($db_config){
        $mysql=new Mysqli($db_config['HOST'],$db_config['USER'],$db_config['PASSWORD'],$db_config['NAME'],$db_config['PORT']);
        if($mysql->errno){
            exit('error');
        }
        $mysql->query("set names utf8mb4");
        return $mysql;
    }
    //执行sql语句
    public function sqlList($sql,$mysql){
        if(!is_object($mysql)){
            $mysql =$this->cqmysqli($this->dbConfig);
        }
        $lista=$mysql->query($sql);
        $list=$lista->fetch_all();
        return $list;
    }
    //获取查找表字段名称，数据类型,注释等信息的sql
    public function getcqsql($table_name){
        $sql=<<<EOT
SELECT
COLUMN_NAME 字段名称,
COLUMN_TYPE 数据类型,
IF(IS_NULLABLE='NO','是','否') AS '必填',
COLUMN_COMMENT 注释,
COLUMN_DEFAULT 默认,
COLUMN_KEY 键值
FROM
INFORMATION_SCHEMA.COLUMNS
where
table_schema ='{$this->dbConfig['NAME']}'  AND
 table_name = '{$table_name}';
EOT;
        return $sql;
    }
    //整合要导出的数据
    public function getcqarr($ReportArr,$v,$list){
        $ReportArr[]=array("");
        $ReportArr[]=array("");
//        $ReportArr[]=array('1','1',"表：{$v[0]}","（{$v[1]}）");
        $ReportArr[]=array('1','1',"{$v[0]}","（{$v[1]}）");
        $ReportArr[]=array(
            '2',
            '2',
            '字段名称',
            '数据类型',
            '必填',
            '注释',
            '默认',
            '键值',
        );
        foreach ($list as $k=>$v){
            array_unshift($v,'','');
            $ReportArr[]=$v;
        }
        return $ReportArr;
    }

    public function getlie($k){
        $arr=array(
            '0'=>'A',
            '1'=>'B',
            '2'=>'C',
            '3'=>'D',
            '4'=>'E',
            '5'=>'F',
            '6'=>'G',
            '7'=>'H',
            '8'=>'I',
            '9'=>'J',
            '10'=>'K',
        );
        return isset($arr[$k])?$arr[$k]:'L';
    }
    //执行查找数据
    public function alltableList(){
        $mysql =$this->cqmysqli($this->dbConfig);
        //获取所有表名及其备注
        $sqltable=<<<EOT
select table_name,TABLE_COMMENT from information_schema.tables where table_schema='{$this->dbConfig['NAME']}';
EOT;
        $tablelist=$this->sqlList($sqltable,$mysql);
        if(empty($tablelist)){
            exit('no such table');
        }
        $ReportArr=array();
        $ReportArr[]=array("");
        $ReportArr[]=array("");
        foreach ($tablelist as $k=>$v){
            $sql=$this->getcqsql($v[0]);
            $list=$this->sqlList($sql,$mysql);
            if($list){
                $ReportArr=$this->getcqarr($ReportArr,$v,$list);
            }
        }
//        $this->prout($ReportArr);
//        $this->toexecl($ReportArr);
        $this->tophpexcel($ReportArr);
    }
    public function tophpexcel($ReportArr){
        //1.文件的创建//创建PHPExcel对象
        $obj=new PHPExcel();
        //创建excel文件写入对象
        $writer = new PHPExcel_Writer_Excel5($obj);
//        //操作工作表(worksheet)//创建工作表方法一
//        $sheet = new PHPExcel_Worksheet($obj, "{$this->dbConfig['NAME']}_".date("Ymd"));
//        $obj->addSheet($sheet);
//        //创建工作表方法二ddddd
//        $obj->createSheet();
        //设置当前工作表,也就是excel从左往右，从0开始
        $obj->setActiveSheetIndex(0);
        //得到当前工作表对象
        $curSheet = $obj->getActiveSheet();
        //设置当前工作表的名称
        $curSheet->setTitle("{$this->dbConfig['NAME']}_".date("Ymd"));
        //往工作表中插入数据
        //设置单元格的值
//        $this->prout($ReportArr);
        foreach ($ReportArr as $k=>$v){
            $n=$k+3;
            if(empty($v) || count($v)==1){
                continue;
            }
            foreach ($v as $ak=>$av){
                if(in_array($ak,array(0,1))){
                    $val='';
                }else{
                    $val=$av;
                }
                $lie=$this->getlie($ak);
                $curSheet->setCellValue("{$lie}{$n}",$val);
            }
            $curSheet->getStyle("F{$n}")->getAlignment()->setWrapText(true);
            if($v[0]==1 && $v[1]==1){
                //单元格的合并
                $curSheet->mergeCells("D{$n}:H{$n}");
                //字体大小
                $curSheet->getStyle("C{$n}:{$lie}{$n}")->getFont()->setSize(14);
                //加粗
                $curSheet->getStyle("C{$n}:{$lie}{$n}")->getFont()->setBold(true);
                //字体颜色
                $curSheet->getStyle("C{$n}:{$lie}{$n}")->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_WHITE));
                //设置填充色
                $curSheet->getStyle("C{$n}:H{$n}")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $curSheet->getStyle("C{$n}:H{$n}")->getFill()->getStartColor()->setARGB('CCCCCCCC');
            }else{
                //设置边框
                $curSheet->getStyle("C{$n}:{$lie}{$n}")->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                //设置边框颜色
                $curSheet->getStyle("C{$n}:{$lie}{$n}")->getBorders()->getTop()->getColor()->setARGB('CCCCCCCC');
            }
            if($v[0]==2 && $v[1]==2){
                $curSheet->getStyle("C{$n}:{$lie}{$n}")->getFont()->setBold(true);
            }

        }
        //设置列宽
        $curSheet->getColumnDimension('C')->setWidth(20);
//            $curSheet->getColumnDimension('C')->setAutoSize(true);
        $curSheet->getColumnDimension('D')->setWidth(14);
//            $curSheet->getColumnDimension('D')->setAutoSize(true);
        $curSheet->getColumnDimension('F')->setWidth(30);
//            $curSheet->getColumnDimension('F')->setAutoSize(true);
        //设置文档标题
        $curSheet->setCellValue('B4',"数据库表（{$this->dbConfig['NAME']}）数据字典说明文档");
        //单元格的合并
        $curSheet->mergeCells('B4:I4');
        //居中
        $curSheet->getStyle('B4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置行高
        $curSheet->getRowDimension('4')->setRowHeight(46);
        //字体大小
        $curSheet->getStyle("B4")->getFont()->setSize(28);
        //加粗
        $curSheet->getStyle("B4")->getFont()->setBold(true);
        //字体颜色
        $curSheet->getStyle("B4")->getFont()->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLACK));
        //设置填充色
//        $curSheet->getStyle("C4:H4")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
//        $curSheet->getStyle("C4:H4")->getFill()->getStartColor()->setARGB('FF008000');
        //冻结单元格
        $curSheet->freezePane('A5');
        ob_end_clean();
        //浏览器输出
        header('Content-Type: application/vnd.ms-execl;charset=utf-8');
        header("Content-Disposition:attachement;filename={$this->dbConfig['NAME']}_".date("Ymd").".xls");
        header('Cache-Control: max-age=0');
        $writer->save('php://output');

    }
    //简易导出excel（可以使用PHPExcel 或者 PhpSpreadsheet导出）
    public function toexecl($ReportArr){
        header("Content-type:application/vnd.ms-excel");
//提示下载
        header("Content-Disposition:attachement;filename={$this->dbConfig['NAME']}_".date("Ymd").".xls");
//报表数据
//        $ReportArr = array(array(1,2,3,4,5),
//            array('A','B','C','D','E'),
//            array('up','down','left','right','center'),
//            array('欢','迎','光','临','郝云云','的','CSDN','博客')
//        );
        $ReportContent = '';
        $num1 = count($ReportArr);
        for($i=0;$i<$num1;$i++){
            $num2 = count($ReportArr[$i]);
            for($j=0;$j<$num2;$j++){
                //ecxel都是一格一格的，用\t将每一行的数据连接起来
                $ReportContent .= '"'.$ReportArr[$i][$j].'"'."\t";
            }
            //最后连接\n 表示换行
            $ReportContent .= "\n";
        }
//用的utf-8 最后转换一个编码为gb
//        $ReportContent = mb_convert_encoding($ReportContent,"gb2312","utf-8");
//输出即提示下载
        echo $ReportContent;
    }

    //打印
    public function prout($list){
        $json_str=json_encode($list,256);
        header("Content-type: application/json");
        header("Access-Control-Allow-Origin: *");
        echo $json_str;exit;
    }

}

//执行
$task = new test();
$task->alltableList();



?>